package ysw.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import ysw.dao.EmployeeDao;
import ysw.databaseconnection.DBConn;
import ysw.vo.Employee;

public class EmployeeJdbc implements EmployeeDao {
 private  Connection conn=null;
    private PreparedStatement psmt=null;
    private ResultSet rs=null;
    public EmployeeJdbc(){}
    public Connection getConn(){
        try{
            if(this.conn==null||this.conn.isClosed()){
                DBConn mc=new DBConn();
                this.conn=mc.getConn();
            }
        }catch(SQLException e){
            e.printStackTrace();
        }
        return conn;
    }

    public Employee addEmployee(Employee employee) {
      String sql="insert into employees values(?,?,?,?,?,?,?,?)";
        try{
     
            psmt=this.getConn().prepareStatement(sql);
    psmt.setInt(1,employee.getEid());
    psmt.setString(2,employee.getEname());
    psmt.setByte(3,employee.getSex());

    psmt.setDouble(4,employee.getSalary());
    psmt.setString(5,employee.getEjob());
    psmt.setByte(6, employee.getDname()); 
    psmt.setString(7, employee.getEphone()); 
    psmt.setBytes(8,employee.getPhoto());
        
            psmt.execute();
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            try{
                if(psmt!=null){
                    psmt.close();
                    psmt=null;
                }
                if(conn!=null){
                    conn.close();
                    conn=null;
                }
            }catch(SQLException e){
                        e.printStackTrace();
                        }
            }
            return employee;
    }

    
    public List showEmployee() throws SQLException {
       String sql="select * from employees";
        List employeeList=new ArrayList();
    try{
        psmt=this.getConn().prepareStatement(sql);
        rs=psmt.executeQuery();
        while(rs.next()){
            Employee employee=new Employee();
           employee.setEid(rs.getInt("eid"));
            employee.setEname(rs.getString("ename"));
            employee.setSex(rs.getByte("sex"));
            
            employee.setSalary(rs.getDouble("salary"));
            employee.setEjob(rs.getString("ejob"));
            employee.setDname(rs.getByte("dname"));
            employee.setEphone(rs.getString("ephone"));
            employee.setPhoto(rs.getBytes("photo"));
            employeeList.add(employee);
     
  
        }
        return employeeList;
    }catch(Exception e){
        e.printStackTrace();
    }finally{
        try{
             if(rs!=null){
                   rs.close();
                   rs=null;
                }
                if(psmt!=null){
                    psmt.close();
                    psmt=null;
                }
                if(conn!=null){
                    conn.close();
                    conn=null;
                }
            }catch(SQLException e){
                        e.printStackTrace();
                        }
            }
            return employeeList;
    }
public Employee showOneEmployee(Integer eid){
     ResultSet rs=null;
    String sql="select * from employees where eid="+eid;
   Employee employee=new Employee();
    try{
      psmt=this.getConn().prepareStatement(sql);
        rs=psmt.executeQuery();
        while(rs.next()){
        
            employee.setEid(rs.getInt("eid"));
            employee.setEname(rs.getString("ename"));
            employee.setSex(rs.getByte("sex"));
            
            employee.setSalary(rs.getDouble("salary"));
            employee.setEjob(rs.getString("ejob"));
            employee.setDname(rs.getByte("dname"));
            employee.setEphone(rs.getString("ephone"));
            employee.setPhoto(rs.getBytes("photo"));
        }
 
    }catch(Exception e){
        e.printStackTrace();
    }finally{
        try{
            psmt.close();
              
            }catch(SQLException e){
                        e.printStackTrace();
                        }
        try{conn.close();
        
        }catch(SQLException e){
             e.printStackTrace();
        }
            }
            return employee;
}

    public void deleteEmployee(Integer eid) {
     String sql="delete from employees where eid="+eid;
    System.out.println(eid+"employeejdbc");
    try{
        psmt=this.getConn().prepareStatement(sql);
         psmt.execute();
    }catch(Exception e){
        e.printStackTrace();
    }finally{
        try{
            psmt.close();
        }catch(SQLException e){
                e.printStackTrace();
        }
        try{
            conn.close();
        }catch(SQLException e){
                e.printStackTrace();
        }
    }  
    }


    public Employee updateSaveEmployee(Employee employee) {
          String sql="update employees set eid=?,ename=?,sex=?,salary=?,ejob=?,dname=?,ephone=?,photo=? where eid="+employee.getEid();
     try{
            
          psmt=this.getConn().prepareStatement(sql);
          
        psmt.setInt(1,employee.getEid());
    psmt.setString(2,employee.getEname());
    psmt.setByte(3,employee.getSex());

    psmt.setDouble(4,employee.getSalary());
    psmt.setString(5,employee.getEjob());
    psmt.setByte(6, employee.getDname()); 
    psmt.setString(7, employee.getEphone()); 
    psmt.setBytes(8,employee.getPhoto());
          
            psmt.execute();
        }catch(Exception e){
            e.printStackTrace();
        }finally{
         try{
             psmt.close();
            
         }catch(SQLException e){
             e.printStackTrace();
         }
     }
     return employee;
}
    }


